﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for Approval
/// </summary>
public class ApprovalDAL
{
    public ApprovalDAL()
	{
	}

    public String CustID {set;get;}
    public String AccountNo { set; get; }
    public String MachineID { set; get; }
    public String Type { set; get; }
    public String StaffID { set; get; }
    public DateTime StartDate { set; get; }
    public String Category { set; get; }
    public DateTime EndDate { set; get; }
   

    public LinkedList<int> levels { set; get; }
    public LinkedList<int> applyInsuredIds { set; get; }

    
    public int ApplicationInsuranceId {set;get; }


    public void approved()
    {
        var conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT  paymentPeriod " +
                      "  " +
                       " FROM  APPLICATION_INSURANCE I where I.applicationID =@ApplicationInsuranceId";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@ApplicationInsuranceId", ApplicationInsuranceId);
        
        SqlDataReader dr = cmd.ExecuteReader();
        String premiumPeriod = "";
       

        if (dr.Read())
        {
            premiumPeriod = dr.GetString(0);

        }
        dr.Close();



        Type = "INSURANCE";
        sql = "insert into ACCOUNTS (custID, accountNo, machineID, type) VALUES (@custID, @accountNo, @machineID, @type)";

        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add(new SqlParameter("@custID", CustID));
        cmd.Parameters.Add(new SqlParameter("@accountNo", AccountNo));
        cmd.Parameters.Add(new SqlParameter("@machineID", DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@type", Type));
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();


        // insert INSURANCE table, the data come from APPLICATION_INSURANCE
        sql = "insert into INSURANCE (accountNo," +
              "custID," +
              "premiumPeriod," +
              "premiumDiscount," +
              "category," +
              "staffID," +
              "productType" +
              ") VALUES (@accountNo," +
              "@custID," +
              "@premiumPeriod," +
              "@premiumDiscount," +
              "@category," +
              "@staffID," +
              "@productType" +
              ")";

        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add(new SqlParameter("@accountNo", AccountNo));
        cmd.Parameters.Add(new SqlParameter("@custID", CustID));
        cmd.Parameters.Add(new SqlParameter("@staffID", StaffID));
        cmd.Parameters.Add(new SqlParameter("@premiumPeriod", premiumPeriod));
        cmd.Parameters.Add(new SqlParameter("@premiumDiscount", DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@category", Category));
        cmd.Parameters.Add(new SqlParameter("@productType", Type));

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();



        for(int i=0;i<applyInsuredIds.Count;i++){
            this.approveInsured(StartDate, EndDate,applyInsuredIds.ElementAt(i), levels.ElementAt(i), AccountNo, conn );
        }


        // UDPATE APPLICATION
        sql = "update APPLICATION_INSURANCE set accountNo=@accountNo, staffID=@staffID,applicationStatus=@applicationStatus,approvalDate=@approvalDate where applicationId=@applicationInsuranceId";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add(new SqlParameter("@accountNo", AccountNo));
        cmd.Parameters.Add(new SqlParameter("@staffID", StaffID));
        cmd.Parameters.Add(new SqlParameter("@applicationInsuranceId", ApplicationInsuranceId));
        cmd.Parameters.Add(new SqlParameter("@applicationStatus", "APPROVED"));
        cmd.Parameters.Add(new SqlParameter("@approvalDate", DateTime.Now));
        cmd.ExecuteNonQuery();


    }



    private void approveInsured(DateTime StartDate, DateTime EndDate, int applicationInsuredId, int level, String AccountNo, SqlConnection conn)
    {
        CalculatePremiumBLL bll = new CalculatePremiumBLL();

        string sql = "select birth,planType,relationship from APPLICATION_INSURANCE_INSURED where insuredId=" + applicationInsuredId;
        var cmd = new SqlCommand(sql, conn);

        SqlDataReader dr = cmd.ExecuteReader();
        DateTime birth = DateTime.MinValue;
        String planType = null;
        String relationship = null;

        if(dr.Read()){
            birth = dr.GetDateTime(0);
            planType = dr.GetString(1);
            relationship = dr.GetString(2);
        }
        dr.Close();

        int age = int.Parse(DateTime.Now.ToString("yyyy")) - int.Parse(birth.ToString("yyyy"));
        Decimal premiun = bll.getPremium(relationship,age, planType, level, 0, Category);

        String startString = StartDate.ToString("dd/MM/yyyy");
        String endString = EndDate.ToString("dd/MM/yyyy");

      
        // insert insued table, the data come from APPLICATION_INSURANCE_INSURED
        sql = "insert into INSURED (" +
                                "accountNo," +
                                "fName," +
                                "lName," +
                                "NRIC," +
                                "nationality," +
                                "gender," +
                                "marital," +
                                "birth," +
                                "relationship," +
                                "planType," +
                                "PremiumLevel," +
                                "occupation," +
                                "telephone," +
                                "longStay," +
                                "mobile," +
                                "email," +
                                "address," +
                                "postalCode," +
                                "employerName," +
                                "employerAddress," +
                                "business," +

                                "illness," +
                                "illDuration," +
                                "illTreatment," +
                                "illhospital," +
                                "DeclinedHistory," +
                                "foreseeable," +
                                "bloodPressureIllSystolic," +
                                "bloodPressureIllDate," +
                                "bloodPressureIllHospital," +
                                "HyperlipidaemiaLevel," +
                                "HyperlipidaemiaDate," +
                                "HyperlipidaemiaHospital," +
                              
                                "Premium," +
                                "policyStatus," +
                                "startDate," +
                                "endDate," +
                                "docPath" +

        ")" +

        "select " +

                            "'" + AccountNo + "'," +
                            "fName," +
                            "lName," +
                            "NRIC," +
                            "nationality," +
                            "gender," +
                            "marital," +
                            "birth," +
                            "relationship," +
                            "planType," +

                            "'" + level + "'," +
                            "occupation," +
                            "telephone," +
                            "longStay," +
                            "mobile," +
                            "email," +
                            "address," +
                            "postalCode," +
                            "employerName," +
                            "employerAddress," +
                            "business," +

                            "illness," +
                            "illDuration," +
                            "illTreatment," +
                            "illhospital," +
                            "DeclinedHistory," +
                            "foreseeable," +
                            "bloodPressureIllSystolic," +
                            "bloodPressureIllDate," +
                            "bloodPressureIllHospital," +
                            "HyperlipidaemiaLevel," +
                            "HyperlipidaemiaDate," +
                            "HyperlipidaemiaHospital," +
                            "'" + premiun + "'," +
                             "'ACTIVE'," +
                              "CONVERT(DATETIME,'"+startString+"' , 103)," + 
                              "CONVERT(DATETIME,'"+endString+"' , 103)," +
                               "docPath" +

                            " from APPLICATION_INSURANCE_INSURED where insuredId=" + applicationInsuredId;

                cmd = new SqlCommand(sql, conn);

               cmd.ExecuteNonQuery();
    }

    public void rejectApplicant(int applicationID)
    {
        String sql = "Update APPLICATION_INSURANCE Set applicationStatus = 'DENIED',staffID ='"+StaffID +"' Where applicationID =" + applicationID;

        var conn = DBManager.getSqlConnection();
        conn.Open();
        var cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();


    }

}